﻿#region << 版 本 注 释 >>
/*----------------------------------------------------------------
 * 修改人：x
 * 时间：
 * 修改说明：
 *----------------------------------------------------------------*/
#endregion << 版 本 注 释 >>using System;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.IdentityModel.Tokens;
using seejee_Backendium.Core.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection.PortableExecutable;
using System.Text;
using System.Threading.Tasks;

namespace seejee_Backendium.Data.Repositores
{
    public class ViewManagementRepository
    {
        private readonly string _connectionString;
        private readonly IConfiguration _configuration;

        public ViewManagementRepository(string connectionString, IConfiguration configuration)
        {
            _connectionString = connectionString;
            _configuration = configuration;
        }
        //获取所有菜单
        public PagedResult<BIMenuDTO> GetBIMenu(int pageIndex, int pageSize, string keyword)
        {
            var menuDictionary = new Dictionary<int, BIMenuDTO>();
            int totalCount = 0;
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();

                // 1. 先查询主表的总数（正确统计主表记录数）
                string countSql = @"
            SELECT COUNT(*) 
            FROM SEBI_MenuConfig 
            WHERE cMenuName = '' OR cMenuName LIKE '%' + @keyword + '%'";
                using (SqlCommand countCmd = new SqlCommand(countSql, connection))
                {
                    countCmd.Parameters.AddWithValue("@keyword", keyword);
                    totalCount = (int)countCmd.ExecuteScalar();
                }

                // 2. 先对主表进行分页，获取当前页的主表ID
                string mainTablePageSql = @"
                    SELECT 
                        mc.ID, 
                        mc.cMenuName, 
                        mc.dCreateTime,
                        mc.sort
                    FROM SEBI_MenuConfig mc 
                    WHERE mc.cMenuName = '' OR mc.cMenuName LIKE '%' + @keyword + '%'
                    ORDER BY mc.sort, mc.cMenuName 
                    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

                // 存储当前页主表的ID，用于后续关联从表
                List<int> currentPageMainIds = new List<int>();
                using (SqlCommand mainCmd = new SqlCommand(mainTablePageSql, connection))
                {
                    mainCmd.Parameters.AddWithValue("@Offset", (pageIndex - 1) * pageSize);
                    mainCmd.Parameters.AddWithValue("@PageSize", pageSize);
                    mainCmd.Parameters.AddWithValue("@keyword", keyword);

                    using (SqlDataReader mainReader = mainCmd.ExecuteReader())
                    {
                        while (mainReader.Read())
                        {
                            int menuId = mainReader.GetInt32(0);
                            currentPageMainIds.Add(menuId);

                            // 初始化主表对应的DTO（新增cCusName赋值）
                            menuDictionary[menuId] = new BIMenuDTO
                            {
                                id = menuId,
                                cMenuName = mainReader.IsDBNull(1) ? "" : mainReader.GetString(1),
                                dCreateTime = mainReader.GetDateTime(2),
                                sort = mainReader.IsDBNull(3) ? 0 : mainReader.GetInt32(3),
                                subMenuCount = 0,
                                Perspectives = new List<PerspectiveNodeDTO>()
                            };
                        }
                    }
                }
                // 如果当前页没有主表数据，直接返回
                if (currentPageMainIds.Count == 0)
                {
                    return new PagedResult<BIMenuDTO>
                    {
                        Items = new List<BIMenuDTO>(),
                        TotalCount = totalCount
                    };
                }

                // 3. 根据当前页的主表ID，关联从表获取Perspectives和subMenuCount
                string joinSql = @"
            SELECT 
                mc.ID, 
                mc1.cPerspectiveName,
                (SELECT COUNT(*) FROM SEBI_MenuConfig_1 WHERE iMenu_id = mc.ID) AS subMenuCount
            FROM SEBI_MenuConfig mc
            LEFT JOIN SEBI_MenuConfig_1 mc1 ON mc1.iMenu_id = mc.ID
            WHERE mc.ID IN ({0})"; // 用主表ID过滤，确保只查询当前页的关联数据

                // 构建IN参数（避免SQL注入）
                string idParams = string.Join(",", currentPageMainIds.Select((_, i) => $"@id{i}"));
                joinSql = string.Format(joinSql, idParams);

                using (SqlCommand joinCmd = new SqlCommand(joinSql, connection))
                {
                    // 添加IN参数
                    for (int i = 0; i < currentPageMainIds.Count; i++)
                    {
                        joinCmd.Parameters.AddWithValue($"@id{i}", currentPageMainIds[i]);
                    }

                    using (SqlDataReader joinReader = joinCmd.ExecuteReader())
                    {
                        while (joinReader.Read())
                        {
                            int menuId = joinReader.GetInt32(0);
                            var currentMenu = menuDictionary[menuId];

                            // 设置子菜单数量（每个主表记录只需要设置一次）
                            currentMenu.subMenuCount = joinReader.IsDBNull(2) ? 0 : joinReader.GetInt32(2);

                            // 添加关联的视图数据
                            if (!joinReader.IsDBNull(1))
                            {
                                string perspectiveName = joinReader.GetString(1);
                                if (!currentMenu.Perspectives.Any(p => p.Name == perspectiveName))
                                {
                                    currentMenu.Perspectives.Add(new PerspectiveNodeDTO
                                    {
                                        Name = perspectiveName
                                    });
                                }
                            }
                        }
                    }
                }
            }

            return new PagedResult<BIMenuDTO>
            {
                Items = menuDictionary.Values.ToList(),
                TotalCount = totalCount
            };
        }
        //获取所有的客户＋id
        public List<CustomerDTO> GetBICustomer()
        {
            var customers = new List<CustomerDTO>();
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();

                // 查询所有客户的ID和名称
                string sql = @"
            SELECT 
                iCusID, 
                cCusName 
            FROM CusUserList
            ORDER BY cCusName";  // 按名称排序，方便前端展示

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            customers.Add(new CustomerDTO
                            {
                                iCusID = reader.GetInt32(0),
                                cCusName = reader.IsDBNull(1) ? "" : reader.GetString(1)
                            });
                        }
                    }
                }
            }

            return customers;
        }

        //修改分类排序
        // 更新菜单排序（与现有代码风格一致）
        public bool UpdateMenuSort(List<MenuSortDTO> sortDtos)
        {
            // 验证参数
            if (sortDtos == null || !sortDtos.Any())
            {
                throw new ArgumentException("排序数据不能为空");
            }

            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                // 开启事务确保所有更新原子性
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        #region 1. 验证所有ID是否存在（避免更新无效记录）
                        // 构建ID参数列表
                        var idParams = new List<SqlParameter>();
                        var idPlaceholders = new List<string>();

                        for (int i = 0; i < sortDtos.Count; i++)
                        {
                            string paramName = $"@Id{i}";
                            idParams.Add(new SqlParameter(paramName, sortDtos[i].Id));
                            idPlaceholders.Add(paramName);
                        }

                        // 查询存在的ID
                        string checkExistSql = $@"
                    SELECT COUNT(1) 
                    FROM SEBI_MenuConfig 
                    WHERE Id IN ({string.Join(",", idPlaceholders)})";

                        using (SqlCommand checkCmd = new SqlCommand(checkExistSql, conn, transaction))
                        {
                            checkCmd.Parameters.AddRange(idParams.ToArray());
                            int existCount = (int)checkCmd.ExecuteScalar();

                            if (existCount != sortDtos.Count)
                            {
                                // 找出无效ID
                                var validIds = new List<int>();
                                string getValidIdsSql = $@"
                            SELECT Id 
                            FROM SEBI_MenuConfig 
                            WHERE Id IN ({string.Join(",", idPlaceholders)})";

                                using (SqlCommand validCmd = new SqlCommand(getValidIdsSql, conn, transaction))
                                {
                                    validCmd.Parameters.AddRange(idParams.ToArray());
                                    using (SqlDataReader reader = validCmd.ExecuteReader())
                                    {
                                        while (reader.Read())
                                        {
                                            validIds.Add(reader.GetInt32(0));
                                        }
                                    }
                                }

                                var invalidIds = sortDtos
                                    .Select(d => d.Id)
                                    .Except(validIds)
                                    .ToList();

                                throw new KeyNotFoundException($"存在无效的菜单ID：{string.Join(",", invalidIds)}");
                            }
                        }
                        #endregion

                        #region 2. 构建批量更新SQL语句
                        // 构建CASE语句实现批量更新
                        var caseBuilder = new StringBuilder();
                        var updateParams = new List<SqlParameter>();

                        for (int i = 0; i < sortDtos.Count; i++)
                        {
                            var dto = sortDtos[i];
                            string idParamName = $"@UpdateId{i}";
                            string sortParamName = $"@Sort{i}";

                            // 添加参数
                            updateParams.Add(new SqlParameter(idParamName, dto.Id));
                            updateParams.Add(new SqlParameter(sortParamName, dto.Sort));

                            // 拼接CASE条件
                            caseBuilder.AppendLine($"WHEN Id = {idParamName} THEN {sortParamName}");
                        }

                        // 完整更新SQL
                        string updateSql = $@"
                    UPDATE SEBI_MenuConfig
                    SET Sort = CASE 
                                   {caseBuilder.ToString()}
                                   ELSE Sort
                               END
                    WHERE Id IN ({string.Join(",", sortDtos.Select((d, i) => $"@UpdateId{i}"))})";
                        #endregion

                        #region 3. 执行更新并提交事务
                        using (SqlCommand cmd = new SqlCommand(updateSql, conn, transaction))
                        {
                            cmd.Parameters.AddRange(updateParams.ToArray());
                            int affectedRows = cmd.ExecuteNonQuery();

                            if (affectedRows == 0)
                            {
                                throw new Exception("排序更新失败，未影响任何记录");
                            }
                        }

                        // 提交事务
                        transaction.Commit();
                        return true;
                        #endregion
                    }
                    catch (Exception ex)
                    {
                        // 出错回滚
                        transaction.Rollback();
                        Console.WriteLine($"排序更新失败：{ex.Message}，堆栈信息：{ex.StackTrace}");
                        throw;  // 向上层抛出异常
                    }
                }
            }
        }


        //获取所有视图名
        //public async Task<IEnumerable<MenuAndViewDTO>> GetAllViewName()
        //{
        //    string connStr = _configuration.GetConnectionString("Default");
        //    // 创建存储结果的列表
        //    var viewNames = new List<MenuAndViewDTO>();
        //    using (SqlConnection connection = new SqlConnection(connStr))
        //    {
        //        await connection.OpenAsync();

        //        string sql = "select cMenuName,cPerspectiveName from SEBI_MenuConfig se right join SEBI_MenuConfig_1 se1 on se.id=se1.iMenu_id";

        //        using (SqlCommand command = new SqlCommand(sql, connection))
        //        {

        //            using (SqlDataReader reader = await command.ExecuteReaderAsync())
        //            {
        //                while (await reader.ReadAsync())
        //                {
        //                    // 读取vViewName字段的值，处理可能的DBNull
        //                    string cmenuName = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
        //                    string cPerspectiveName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
        //                    // 只添加非空的视图名称（根据实际业务调整条件）
        //                    if (!string.IsNullOrEmpty(cPerspectiveName))
        //                    {
        //                        viewNames.Add(new MenuAndViewDTO
        //                        {
        //                            cMenuName = cmenuName,
        //                            cPerspectiveName = cPerspectiveName
        //                        });
        //                    }
        //                }
        //            }
        //        }
        //    }
        //    return viewNames;

        //}
        // 修改方法签名，添加 iCusID 筛选参数
        public async Task<IEnumerable<MenuAndViewDTO>> GetAllViewName(int? iCusID = null)
        {
            string connStr = _configuration.GetConnectionString("Default");
            var viewNames = new List<MenuAndViewDTO>();

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                await connection.OpenAsync();

                // 1. 构建SQL查询（包含cCusUserListId字段用于筛选，添加条件占位符）
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.Append(@"
            select 
                se.cMenuName, 
                se1.cPerspectiveName, 
                se.cCusUserListId 
            from SEBI_MenuConfig se 
            right join SEBI_MenuConfig_1 se1 on se.id = se1.iMenu_id
        ");

                // 2. 根据iCusID是否有值，动态添加筛选条件
                List<SqlParameter> parameters = new List<SqlParameter>();
                if (iCusID.HasValue)
                {
                    sqlBuilder.Append(" where se.cCusUserListId = @iCusID"); // 筛选视图关联的客户ID
                    parameters.Add(new SqlParameter("@iCusID", iCusID.Value));
                }

                // 3. 执行参数化查询
                using (SqlCommand command = new SqlCommand(sqlBuilder.ToString(), connection))
                {
                    command.Parameters.AddRange(parameters.ToArray()); // 添加参数

                    using (SqlDataReader reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            // 读取字段（包含cCusUserListId）
                            string cmenuName = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
                            string cPerspectiveName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
                            int? cCusUserListId = reader.IsDBNull(2) ? null : (int?)reader.GetInt32(2);

                            // 只添加有效视图（根据实际业务调整）
                            if (!string.IsNullOrEmpty(cPerspectiveName))
                            {
                                viewNames.Add(new MenuAndViewDTO
                                {
                                    cMenuName = cmenuName,
                                    cPerspectiveName = cPerspectiveName,
                                    cCusUserListId = cCusUserListId // 传递客户关联ID
                                });
                            }
                        }
                    }
                }
            }
            return viewNames;
        }
        //根据id获取视图，用于回显
        public async Task<MenuInsertDTO> GetView(int id)
        {
            // 初始化返回对象
            var menuInsertDto = new MenuInsertDTO();
            menuInsertDto.Id = id;
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                await connection.OpenAsync();

                // 执行查询的SQL语句
                string sql = @"select s.cMenuName, s1.cPerspectiveName 
                       from SEBI_MenuConfig s 
                       left join SEBI_MenuConfig_1 s1 on s.id = s1.iMenu_id 
                       where s.id = @id";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // 添加参数并指定类型，避免SQL注入和类型问题
                    command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { Value = id });

                    using (SqlDataReader reader = await command.ExecuteReaderAsync())
                    {
                        string menuName = null;

                        while (await reader.ReadAsync())
                        {
                            // 读取菜单名称（只需要设置一次）
                            if (menuName == null)
                            {

                                menuName = reader.IsDBNull(0) ? "" : reader.GetString(0);
                                menuInsertDto.cMenuName = menuName;
                            }

                            // 读取视图名称并添加到集合
                            if (!reader.IsDBNull(1))
                            {
                                string perspectiveName = reader.GetString(1);
                                menuInsertDto.Perspectives.Add(new PerspectiveNodeDTO
                                {
                                    // 这里假设没有单独的ID字段，可根据实际情况调整
                                    //Id = Guid.NewGuid().ToString(), // 临时生成唯一ID
                                    Name = perspectiveName
                                    // 不使用二级视图，Children保持默认空列表
                                });
                            }
                        }
                    }
                }
            }

            return menuInsertDto;
        }

        //新增分类
        public bool InsertMenu(MenuInsertDTO menus)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                // 开启事务（所有表插入在同一事务中，确保数据一致性）
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        #region 新增：查询cMenuName是否已存在
                        int MenuId = 0;
                        string MenuSql = @"
                    SELECT id FROM SEBI_MenuConfig 
                    WHERE cMenuName = @cMenuName;";

                        using (SqlCommand checkCmd = new SqlCommand(MenuSql, conn, transaction))
                        {
                            checkCmd.Parameters.AddWithValue("@cMenuName",
                                string.IsNullOrEmpty(menus.cMenuName) ? (object)DBNull.Value : menus.cMenuName);

                            var checkResult = checkCmd.ExecuteScalar();
                            if (checkResult != null && checkResult != DBNull.Value)
                            {
                                // 存在相同cMenuName，获取已有主键
                                MenuId = Convert.ToInt32(checkResult);
                            }
                        }
                        #endregion
            

                        #region 如果存在cMenuName，直接插入子表并返回
                        if (MenuId > 0)
                        {
                            if (menus.Perspectives != null && menus.Perspectives.Any())
                            {
                                foreach (var perspective in menus.Perspectives)
                                {
                                    // 1. 检查子表是否已存在相同记录（iMenu_id和cPerspectiveName组合）
                                    string checkChildSql = @"
                          SELECT COUNT(1) FROM SEBI_MenuConfig_1 
                          WHERE iMenu_id = @iMenu_id AND cPerspectiveName = @cPerspectiveName;";

                                    using (SqlCommand checkChildCmd = new SqlCommand(checkChildSql, conn, transaction))
                                    {
                                        checkChildCmd.Parameters.AddWithValue("@iMenu_id", MenuId);
                                        checkChildCmd.Parameters.AddWithValue("@cPerspectiveName",
                                            string.IsNullOrEmpty(perspective.Name) ? (object)DBNull.Value : perspective.Name);

                                        int existingCount = Convert.ToInt32(checkChildCmd.ExecuteScalar());

                                        // 2. 如果存在记录，先删除
                                        if (existingCount > 0)
                                        {
                                            string deleteChildSql = @"
                                  DELETE FROM SEBI_MenuConfig_1 
                                  WHERE iMenu_id = @iMenu_id AND cPerspectiveName = @cPerspectiveName;";

                                            using (SqlCommand deleteCmd = new SqlCommand(deleteChildSql, conn, transaction))
                                            {
                                                deleteCmd.Parameters.AddWithValue("@iMenu_id", MenuId);
                                                deleteCmd.Parameters.AddWithValue("@cPerspectiveName",
                                                    string.IsNullOrEmpty(perspective.Name) ? (object)DBNull.Value : perspective.Name);

                                                deleteCmd.ExecuteNonQuery();
                                            }
                                        }
                                    }
                                    string insertPerspectiveSql = @"
                                INSERT INTO SEBI_MenuConfig_1
                                (iMenu_id, cPerspectiveName) 
                                VALUES (@iMenu_id, @cPerspectiveName);";

                                    using (SqlCommand cmd = new SqlCommand(insertPerspectiveSql, conn, transaction))
                                    {
                                        cmd.Parameters.AddWithValue("@iMenu_id", MenuId);
                                        cmd.Parameters.AddWithValue("@cPerspectiveName",
                                            string.IsNullOrEmpty(perspective.Name) ? (object)DBNull.Value : perspective.Name);

                                        cmd.ExecuteNonQuery();
                                    }
                                }
                            }
                            transaction.Commit();
                            return true;
                        }
                        #endregion

                        #region 0. 获取主表中sort字段的最大值并加1
                        int newSortValue = 1; // 默认值，若表为空则从1开始
                        string getMaxSortSql = @"
                    SELECT MAX(sort) FROM SEBI_MenuConfig;";

                        using (SqlCommand maxSortCmd = new SqlCommand(getMaxSortSql, conn, transaction))
                        {
                            var maxSortResult = maxSortCmd.ExecuteScalar();
                            // 如果存在现有记录，在最大值基础上加1
                            if (maxSortResult != DBNull.Value && maxSortResult != null)
                            {
                                if (int.TryParse(maxSortResult.ToString(), out int currentMaxSort))
                                {
                                    newSortValue = currentMaxSort + 1;
                                }
                            }
                        }
                        #endregion
                        #region 1. 插入主表（SEBI_MenuConfig）并获取主键
                        int iMenu_id = 0; // 主表主键
                        string insertMainSql = @"
            INSERT INTO SEBI_MenuConfig
            (cMenuName, dCreateTime,sort) 
            VALUES (@cMenuName, GETDATE(),@sort);
            SELECT SCOPE_IDENTITY();"; // 获取自增主键

                        using (SqlCommand cmd = new SqlCommand(insertMainSql, conn, transaction))
                        {
                            // 添加参数（处理空值）
                            cmd.Parameters.AddWithValue("@cMenuName", string.IsNullOrEmpty(menus.cMenuName) ? (object)DBNull.Value : menus.cMenuName);
                         
                            cmd.Parameters.AddWithValue("@sort", newSortValue); // 添加sort参数
                            // 执行并获取主表主键（SCOPE_IDENTITY()返回decimal，需转换为int）
                            var result = cmd.ExecuteScalar();
                            if (result == null || !int.TryParse(result.ToString(), out iMenu_id))
                            {
                                throw new Exception("插入主表失败，无法获取主键iMenuId");
                            }
                        }
                        #endregion


                        #region 2. 插入子表（SEBI_MenuConfig_1，一级视图）并关联主表
                        if (menus.Perspectives != null && menus.Perspectives.Any())
                        {
                            foreach (var perspective in menus.Perspectives)
                            {
                                int iPerspective_id = 0; // 子表主键
                                string insertPerspectiveSql = @"
                    INSERT INTO SEBI_MenuConfig_1
                    (iMenu_id, cPerspectiveName) 
                    VALUES (@iMenu_id, @cPerspectiveName);
                    SELECT SCOPE_IDENTITY();"; // 获取子表主键

                                using (SqlCommand cmd = new SqlCommand(insertPerspectiveSql, conn, transaction))
                                {
                                    cmd.Parameters.AddWithValue("@iMenu_id", iMenu_id); // 关联主表
                                                                                        // 修复：使用Name属性（对应DTO中的定义）
                                    cmd.Parameters.AddWithValue("@cPerspectiveName", string.IsNullOrEmpty(perspective.Name) ? (object)DBNull.Value : perspective.Name);

                                    // 获取子表主键
                                    var result = cmd.ExecuteScalar();
                                    if (result == null || !int.TryParse(result.ToString(), out iPerspective_id))
                                    {
                                        // 修复：使用Name属性
                                        throw new Exception($"插入一级视图[{perspective.Name}]失败，无法获取主键iPerspectiveId");
                                    }
                                }
                            }
                        }
                        #endregion
                        // 所有表插入成功，提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        // 任何一步失败，回滚所有操作
                        transaction.Rollback();
                        Console.WriteLine($"插入失败：{ex.Message}，堆栈信息：{ex.StackTrace}");
                        throw; // 抛出异常给上层处理
                    }
                }
            }
        }
        //修改视图分类
        public bool UpdateView(int id, MenuInsertDTO menu)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                // 开启事务（确保主表和子表操作原子性）
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        #region 1. 更新主表（SEBI_MenuConfig）
                        string updateMainSql = @"
                UPDATE SEBI_MenuConfig
                SET cMenuName = @cMenuName 
                WHERE id = @id;";

                        using (SqlCommand cmd = new SqlCommand(updateMainSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@id", id);  // 主键参数（方法传入的id）
                            cmd.Parameters.AddWithValue("@cMenuName", string.IsNullOrEmpty(menu.cMenuName) ? (object)DBNull.Value : menu.cMenuName);

                            int mainRows = cmd.ExecuteNonQuery();
                            if (mainRows == 0)
                            {
                                throw new Exception("主表更新失败，未找到对应菜单");
                            }
                        }
                        #endregion


                        #region 2. 删除原有子表数据（先删二级，再删一级，避免外键约束）
                        // 2.1 删除二级子表（SEBI_MenuConfig_2）
                        //        string deleteSecondarySql = @"
                        //DELETE FROM SEBI_MenuConfig_2
                        //WHERE iPerspective_id IN (
                        //    SELECT iPerspective_id FROM SEBI_MenuConfig_1 
                        //    WHERE iMenu_id = @iMenu_id
                        //);";

                        //        using (SqlCommand cmd = new SqlCommand(deleteSecondarySql, conn, transaction))
                        //        {
                        //            cmd.Parameters.AddWithValue("@iMenu_id", id);
                        //            cmd.ExecuteNonQuery();  // 即使没有数据也不报错
                        //        }

                        // 2.2 删除一级子表（SEBI_MenuConfig_1）
                        string deletePerspectiveSql = @"
                DELETE FROM SEBI_MenuConfig_1
                WHERE iMenu_id = @iMenu_id;";

                        using (SqlCommand cmd = new SqlCommand(deletePerspectiveSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@iMenu_id", id);
                            cmd.ExecuteNonQuery();  // 即使没有数据也不报错
                        }
                        #endregion


                        #region 3. 重新插入新的子表数据（与插入逻辑一致）
                        if (menu.Perspectives != null && menu.Perspectives.Any())
                        {
                            foreach (var perspective in menu.Perspectives)
                            {
                                int iPerspective_id = 0;
                                // 3.1 插入一级视图
                                string insertPerspectiveSql = @"
                        INSERT INTO SEBI_MenuConfig_1
                        (iMenu_id, cPerspectiveName) 
                        VALUES (@iMenu_id, @cPerspectiveName);
                        SELECT SCOPE_IDENTITY();";

                                using (SqlCommand cmd = new SqlCommand(insertPerspectiveSql, conn, transaction))
                                {
                                    cmd.Parameters.AddWithValue("@iMenu_id", id);  // 关联主表主键（不变）
                                    cmd.Parameters.AddWithValue("@cPerspectiveName", string.IsNullOrEmpty(perspective.Name) ? (object)DBNull.Value : perspective.Name);

                                    var result = cmd.ExecuteScalar();
                                    if (result == null || !int.TryParse(result.ToString(), out iPerspective_id))
                                    {
                                        throw new Exception($"插入一级视图[{perspective.Name}]失败");
                                    }
                                }

                                // 3.2 插入二级视图
                                //if (perspective.Children != null && perspective.Children.Any())
                                //{
                                //    foreach (var secondary in perspective.Children)
                                //    {
                                //        string insertSecondarySql = @"
                                //INSERT INTO SEBI_MenuConfig_2
                                //(iPerspective_id, cSubPerspectiveName) 
                                //VALUES (@iPerspective_id, @cSubPerspectiveName);";

                                //        using (SqlCommand cmd = new SqlCommand(insertSecondarySql, conn, transaction))
                                //        {
                                //            cmd.Parameters.AddWithValue("@iPerspective_id", iPerspective_id);  // 关联一级视图主键
                                //            cmd.Parameters.AddWithValue("@cSubPerspectiveName", string.IsNullOrEmpty(secondary.Name) ? (object)DBNull.Value : secondary.Name);
                                //            cmd.ExecuteNonQuery();
                                //        }
                                //    }
                                //}
                            }
                        }
                        #endregion


                        // 所有操作成功，提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        // 出错回滚所有操作
                        transaction.Rollback();
                        Console.WriteLine($"更新失败：{ex.Message}，堆栈信息：{ex.StackTrace}");
                        throw;  // 向上层抛出异常
                    }
                }
            }

        }
        //删除视图分类
        public bool DeleteView(int id)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                // 开启事务，确保所有删除操作原子性
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        #region  删除一级子表（SEBI_MenuConfig_1）
                        // 依赖主表的主键iMenu_id
                        string deletePerspectiveSql = @"
                DELETE FROM SEBI_MenuConfig_1
                WHERE iMenu_id = @iMenu_id;";

                        using (SqlCommand cmd = new SqlCommand(deletePerspectiveSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@iMenu_id", id);
                            cmd.ExecuteNonQuery();
                        }
                        #endregion

                        #region  删除主表（SEBI_MenuConfig）
                        string deleteMainSql = @"
                DELETE FROM SEBI_MenuConfig
                WHERE id = @id;";

                        using (SqlCommand cmd = new SqlCommand(deleteMainSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@id", id);
                            int mainRows = cmd.ExecuteNonQuery();
                            // 检查主表是否存在该记录
                            if (mainRows == 0)
                            {
                                throw new Exception("删除失败：未找到对应的菜单分类");
                            }
                        }
                        #endregion

                        // 所有删除操作成功，提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        // 任何步骤失败，回滚所有操作
                        transaction.Rollback();
                        Console.WriteLine($"删除菜单分类失败：{ex.Message}，堆栈信息：{ex.StackTrace}");
                        throw; // 向上层抛出异常，便于前端捕获错误
                    }
                }
            }
        }
        public bool DeleteView(string categoryName, string viewName)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        #region 查询主表ID
                        string getMainIdSql = @"
                            SELECT id 
                            FROM SEBI_MenuConfig 
                            WHERE cMenuName = @CategoryName;";

                        int mainId;
                        using (SqlCommand cmd = new SqlCommand(getMainIdSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@CategoryName", categoryName);
                            var result = cmd.ExecuteScalar();

                            if (result == null)
                            {
                                throw new Exception($"未找到名为'{categoryName}'的菜单分类");
                            }

                            mainId = Convert.ToInt32(result);
                        }
                        #endregion

                        #region 根据主表ID和viewName删除子表内容
                        string deleteSubSql = @"
                            DELETE FROM SEBI_MenuConfig_1
                            WHERE iMenu_id = @iMenu_id 
                              AND cPerspectiveName = @ViewName;";

                        using (SqlCommand cmd = new SqlCommand(deleteSubSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@iMenu_id", mainId);
                            cmd.Parameters.AddWithValue("@ViewName", viewName);

                            int subRows = cmd.ExecuteNonQuery();

                            if (subRows == 0)
                            {
                                throw new Exception($"未找到分类'{categoryName}'下名为'{viewName}'的视图");
                            }
                        }
                        #endregion

                        transaction.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"删除视图失败：{ex.Message}，堆栈信息：{ex.StackTrace}");
                        throw;
                    }
                }
            }
        }

        // 删除视图的所有内容（即使中间步骤失败也继续执行后续操作）
        public bool DeleteViews(string viewName)
        {
            string connStr = _configuration.GetConnectionString("Default");
            bool hasSuccessOperation = false; // 记录是否有成功执行的操作
            int BIMainTableID = 0; // 主表ID（可能获取失败）
            List<int> subTableIds = new List<int>(); // 子表ID列表（可能获取失败）

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open(); // 打开连接（单独捕获连接异常）
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"数据库连接失败：{ex.Message}");
                    // 连接失败仍尝试继续（实际可能无法执行后续操作，但按需求继续流程）
                }

                // 查询主表ID（独立异常捕获）
                try
                {
                    string getMainTableSql = @"
                SELECT Id
                FROM SEBI_BIMainTable 
                WHERE Name = @viewName;";

                    using (SqlCommand cmd = new SqlCommand(getMainTableSql, conn))
                    {
                        cmd.Parameters.AddWithValue("@viewName", viewName);
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                BIMainTableID = reader.GetInt32(0);
                                hasSuccessOperation = true; // 标记成功操作
                            }
                            else
                            {
                                Console.WriteLine("未找到对应的视图主表记录");
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"查询主表ID失败：{ex.Message}");
                    // 捕获异常后继续执行后续步骤
                }

                // 查询子表ID列表（独立异常捕获）
                try
                {
                    if (BIMainTableID > 0) // 只有主表ID有效时才查询
                    {
                        string getSubTableIdsSql = @"
                    SELECT Id 
                    FROM SEBI_Container
                    WHERE BIMainTableID = @BIMainTableID;";

                        using (SqlCommand cmd = new SqlCommand(getSubTableIdsSql, conn))
                        {
                            cmd.Parameters.AddWithValue("@BIMainTableID", BIMainTableID);
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    subTableIds.Add(reader.GetInt32(0));
                                }
                            }
                        }
                        hasSuccessOperation = true; // 标记成功操作
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"查询子表ID失败：{ex.Message}");
                    // 继续执行后续步骤
                }

                // 删除图表配置表（独立异常捕获）
                try
                {
                    if (subTableIds.Any()) // 只有子表ID存在时才删除
                    {
                        string idParams = string.Join(",", subTableIds.Select((_, i) => $"@id{i}"));
                        var chartTables = new List<string>
                {
                    "SEBI_BarConfig", "SEBI_PieConfig", "SEBI_TableConfig",
                    "SEBI_TextConfig", "SEBI_FlipNumberConfig", "SEBI_RingConfig", "SEBI_LineConfig"
                };

                        foreach (var table in chartTables)
                        {
                            string deleteChartSql = $@"
                        DELETE FROM {table} 
                        WHERE ContainerId IN ({idParams});";

                            using (SqlCommand cmd = new SqlCommand(deleteChartSql, conn))
                            {
                                for (int i = 0; i < subTableIds.Count; i++)
                                {
                                    cmd.Parameters.AddWithValue($"@id{i}", subTableIds[i]);
                                }
                                cmd.ExecuteNonQuery();
                            }
                        }
                        hasSuccessOperation = true; // 标记成功操作
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"删除图表配置表失败：{ex.Message}");
                    // 继续执行后续步骤
                }

                //  删除一级子表（独立异常捕获）
                try
                {
                    if (BIMainTableID > 0) // 只有主表ID有效时才删除
                    {
                        string deleteSubTableSql = @"
                    DELETE FROM SEBI_Container 
                    WHERE BIMainTableID = @BIMainTableID;";

                        using (SqlCommand cmd = new SqlCommand(deleteSubTableSql, conn))
                        {
                            cmd.Parameters.AddWithValue("@BIMainTableID", BIMainTableID);
                            cmd.ExecuteNonQuery();
                        }
                        hasSuccessOperation = true; // 标记成功操作
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"删除子表失败：{ex.Message}");
                    // 继续执行后续步骤
                }

                // 删除主表（独立异常捕获）
                try
                {
                    string deleteMainTableSql = @"
                DELETE FROM SEBI_BIMainTable 
                WHERE Name = @viewName;";

                    using (SqlCommand cmd = new SqlCommand(deleteMainTableSql, conn))
                    {
                        cmd.Parameters.AddWithValue("@viewName", viewName);
                        int rowsAffected = cmd.ExecuteNonQuery();
                        if (rowsAffected > 0)
                        {
                            hasSuccessOperation = true; // 标记成功操作
                        }
                        else
                        {
                            Console.WriteLine("主表记录删除失败（可能不存在）");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"删除主表失败：{ex.Message}");
                    // 最后一步失败也不影响流程结束
                }
            }

            // 返回是否有至少一个操作成功（根据实际需求调整）
            return hasSuccessOperation;
        }

    }
}
